﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml;
using System.Collections;
using System.Collections.Generic;

namespace WebSite.QuestionnairHandle
{

    /// <summary>
    ///QuestionnaireHandler 的摘要说明
    /// </summary>
    public class QuestionnaireHandler
    {
        SqlConnection conn;
        public int flag = 0;
        public QuestionnaireHandler()
        {
            conn = new SqlConnection("server=localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
            conn.Open();
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        //返回所有问卷
        public DataSet GetAllQuestionnaire()
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetAllQuestionnaire";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }
        public int AddQuestionnaire(string QuestionaireName, DateTime ItemDate, string UserId, string Location)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "AddQuestionnaire";
                command.CommandType = CommandType.StoredProcedure;
                //添加输入参数列表
                SqlParameter[] ArrayParams = {
                                   new SqlParameter("@QuestionaireName",QuestionaireName),
                                   new SqlParameter("@ItemDate",ItemDate),
                                   new SqlParameter("@UserId",UserId),
                                   new SqlParameter("@Location",Location),
                               };
                foreach (SqlParameter param in ArrayParams)
                {
                    command.Parameters.Add(param);
                }
                SqlParameter para = new SqlParameter("@QuestionnaireId", SqlDbType.Int);
                para.Direction = ParameterDirection.Output;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                return (int)para.Value;

            }
            catch
            {
                throw;
            }

        }

        public DataSet GetQuestionById(int QuestionnaireId)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetQuestionnaireById";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@QuestionnaireId", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = QuestionnaireId;
                da.SelectCommand.Parameters.Add(param);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }

        public DataSet GetQuestionByUserId(string UserId)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetAllQuestionnaireByTeacherId";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@UserId", SqlDbType.Text);
                param.Direction = ParameterDirection.Input;
                param.Value = UserId;
                da.SelectCommand.Parameters.Add(param);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }

        public bool DeleteQuestionnaireById(int QuestionnaireId)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "DeleteQuestionnaire";
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@QuestionnaireId", QuestionnaireId);
                param.Direction = ParameterDirection.Input;
                command.Parameters.Add(param);
                param = new SqlParameter("@ReturnValue", SqlDbType.Int);
                param.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(param);
                command.ExecuteNonQuery();
                if ((int)param.Value == 1)
                {
                    return true;
                }
                else return false;
            }
            catch
            {
                throw;
            }
        }

        public bool DeleteQuestionnaireResultsById(int QuestionnaireId)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "DeleteQuestioResultsByQuestionId";
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@QuestionnaireId", QuestionnaireId);
                param.Direction = ParameterDirection.Input;
                command.Parameters.Add(param);
                param = new SqlParameter("@ReturnValue", SqlDbType.Int);
                param.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(param);
                command.ExecuteNonQuery();
                if ((int)param.Value == 1)
                {
                    return true;
                }
                else return false;
            }
            catch
            {
                throw;
            }
        }

        public bool ModifyQuestionnaire(int QuestionnaireId, string QuestionaireName, DateTime ItemDate, string UserId, string Location)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "ModifyQuestionnaire";
                command.CommandType = CommandType.StoredProcedure;
                //添加输入参数列表
                SqlParameter[] ArrayParams = {
                                   new SqlParameter("@QuestionaireName",QuestionaireName),
                                   new SqlParameter("@ItemDate",ItemDate),
                                   new SqlParameter("@UserId",UserId),
                                   new SqlParameter("@Location",Location),
                                   new SqlParameter("@QuestionnaireId",QuestionnaireId),

                               };
                foreach (SqlParameter param in ArrayParams)
                {
                    command.Parameters.Add(param);
                }
                SqlParameter para = new SqlParameter("@ReturnValue", SqlDbType.Int);
                para.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                if ((int)para.Value == 1)
                {
                    return true;
                }
                else return false;
            }
            catch
            {
                throw;
            }
        }


        public int AddQuestionnaireResult(int Id, string UserId, int QuestionNum, string Result)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "AddQuestionnaireResult";
                command.CommandType = CommandType.StoredProcedure;
                //添加输入参数列表
                SqlParameter[] ArrayParams = {
                                   new SqlParameter("@Id",Id),
                                   new SqlParameter("@UserId",UserId),
                                   new SqlParameter("@QuestionNum",QuestionNum),
                                   new SqlParameter("@Result",Result),
                               };
                foreach (SqlParameter param in ArrayParams)
                {
                    command.Parameters.Add(param);
                }
                SqlParameter para = new SqlParameter("@ReturnValue", SqlDbType.Int);
                para.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                return (int)para.Value;

            }
            catch
            {
                //throw;
                flag = 1;
                return 0;
            }

        }



        public DataSet GetQuestionnaireResultById(int QuestionnaireId)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetQuestionnaireResultById";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@QuestionnaireId", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = QuestionnaireId;
                da.SelectCommand.Parameters.Add(param);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 获得该问卷的第num道题目相关信息
        /// </summary>
        /// <param name="FileName">问卷xml文件的内容</param>
        /// <param name="num">要获得的问卷题目</param>
        /// <returns>存有该题目的Dierctionary<string,string></returns>
        public Dictionary<string, string> GetQuestionByNum(string FileName, int num)
        {
            try
            {
                Dictionary<string, string> Content = new Dictionary<string, string>();
                //如果存在问卷xml文件，则进行读取
                if (System.IO.File.Exists(GetQuestionPath(FileName)))
                {
                    using (XmlReader reader = XmlReader.Create(GetQuestionPath(FileName)))
                    {
                        bool IsBegin = false;
                        while (reader.Read())
                        {
                            //只处理节点信息
                            if (reader.NodeType == XmlNodeType.Element)
                            {
                                //如果到了下一道题，则退出
                                if (IsBegin && reader.LocalName == "question")
                                {
                                    break;
                                }
                                if (!IsBegin && reader.LocalName == "question")
                                {
                                    //如果是我们要找的题目
                                    if (reader.MoveToAttribute(0))
                                    {
                                        if (reader.Name == "num" && reader.Value == num.ToString())
                                        {
                                            IsBegin = true;
                                            Content["TakingPartNum"] = reader.GetAttribute("TakingPartNum");
                                            Content["QuestionContent"] = reader.GetAttribute("content");
                                        }
                                    }
                                }
                                if (IsBegin)
                                {
                                    if (reader.LocalName == "Item")
                                    {
                                        Content[reader.ReadString()] = reader.GetAttribute("selectedNum");
                                    }
                                }
                            }
                        }
                    }
                }

                return Content;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 在问卷中增加题目
        /// </summary>
        /// <param name="FileName">问卷文件名</param>
        /// <param name="Content">题目内容</param>
        /// <param name="num">题目编号</param>
        public void AddQuestion(string FileName, Dictionary<string, string> Content, int num)
        {
            try
            {
                XmlDocument xmlDoc;
                xmlDoc = new XmlDocument();
                //如果不存在，则新建一个xml文件
                if (!System.IO.File.Exists(GetQuestionPath(FileName)))
                {
                    xmlDoc.LoadXml("<questionnair questionnum=\"0\"></questionnair>");
                    xmlDoc.Save(GetQuestionPath(FileName));
                }
                xmlDoc.Load(GetQuestionPath(FileName));

                XmlNode node = xmlDoc.SelectSingleNode("questionnair");
                int count = int.Parse(node.Attribute["questionnum"].Value) + 1;
                XmlElement xe = (XmlElement)node;
                xe.SetAttribute("questionnum", count.ToString());

                //设置question节点的属性
                XmlElement element = xmlDoc.CreateElement("question");
                element.SetAttribute("num", Content["num"]);
                element.SetAttribute("TakingPartNum", Content["TakingPartNum"]);
                element.SetAttribute("content", Content["content"]);
                Content.Remove("question");
                Content.Remove("TakingPartNum");
                Content.Remove("content");
                int i = 1;
                //添加选项
                foreach (KeyValuePair<string, string> e in Content)
                {
                    XmlElement item = new XmlElement();
                    item.SetAttribute("num", i.ToString());
                    i++;
                    item.SetAttribute("selectedNum", e.Value);
                    item.LocalName = "Item";
                    item.InnerText = e.Key;
                    element.AppendChild(item);
                }
                xe.AppendChild(element);

                xmlDoc.Save(GetQuestionPath(FileName));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更改问卷
        /// </summary>
        /// <param name="FileName">问卷文件名</param>
        /// <param name="Content">问卷内容</param>
        /// <param name="num">题目编号</param>
        public void ModifyQuestion(string FileName, Dictionary<string, string> Content, int num)
        {
            try
            {
                XmlDocument xmlDoc;
                xmlDoc = new XmlDocument();
                //如果不存在，直接返回了。
                if (!System.IO.File.Exists(GetQuestionPath(FileName)))
                {
                    return;
                }
                xmlDoc.Load(GetQuestionPath(FileName));

                XmlNode node = xmlDoc.SelectSingleNode("questionnair");
                int count = int.Parse(node.Attribute["questionnum"].Value);

                //如果没有那么多道题目，直接返回
                if (count < num)
                {
                    return;
                }
                XmlNodeList nodeList = node.ChildNodes;
                foreach (XmlNode xn1 in nodeList)
                {
                    XmlElement xe1 = (XmlElement)xn1;
                    if (xe1.GetAttribute("num") == num.ToString() && xe1.LocalName == "question")
                    {
                        xe1.SetAttribute("TakingPartNum", Content["TakingPartNum"]);
                        xe1.SetAttribute("content", Content["content"]);
                        Content.Remove("TakingPartNum");
                        Content.Remove("content");
                        XmlNodeList list = xe1.ChildNodes;
                        foreach (XmlNode xn2 in list)
                        {
                            xn2.ParentNode.RemoveChild(xn2);
                        }
                        int i = 1;
                        foreach (KeyValuePair<string, string> e in Content)
                        {
                            XmlElement xe2 = new XmlElement();
                            xe2.LocalName = "Item";
                            xe2.SetAttribute("num", i.ToString());
                            i++;
                            xe2.SetAttribute("selectedNum", e.Value);
                            xe2.Value = e.Key;
                            xe1.AppendChild(xe2);
                        }
                        break;
                    }
                }
                xmlDoc.Save(GetQuestionPath(FileName));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除问卷中第num个题目
        /// </summary>
        /// <param name="FileName">文件名</param>
        /// <param name="num">要删除的题目</param>
        public void RemoveQuestion(string FileName, int num)
        {
            try
            {
                XmlDocument xmlDoc;
                xmlDoc = new XmlDocument();
                //如果不存在，直接返回了。
                if (!System.IO.File.Exists(GetQuestionPath(FileName)))
                {
                    return;
                }
                xmlDoc.Load(GetQuestionPath(FileName));


                XmlNode node = xmlDoc.SelectSingleNode("questionnair");
                int count = int.Parse(node.Attribute["questionnum"].Value);
                //如果没有那么多道题目，直接返回
                if (count < num)
                {
                    return;
                }
                //题目数减一。
                XmlElement xe = (XmlElement)node;
                xe.SetAttribute("questionnum", (count - 1).ToString());

                XmlNodeList nodeList = node.ChildNodes;
                foreach (XmlNode xn1 in nodeList)
                {
                    XmlElement xe1 = (XmlElement)xn1;
                    //找到并删除这个节点。
                    if (xe1.GetAttribute("num") == num.ToString() && xe1.LocalName == "question")
                    {
                        xe1.ParentNode.RemoveChild(xe1);
                        break;
                    }
                }
                xmlDoc.Save(GetQuestionPath(FileName));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获得问卷中题目的总数
        /// </summary>
        /// <param name="FileName">问卷xml文件名</param>
        /// <returns>问卷中题目数量</returns>
        public int QuestionCount(string FileName)
        {
            if (System.IO.File.Exists(GetQuestionPath(FileName)))
            {
                XmlReader reader = XmlReader.Create(GetQuestionPath(FileName));
                while (reader.Read())
                {
                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        if (reader.LocalName == "questionnair")
                        {
                            return int.Parse(reader.GetAttribute("questionnum"));
                        }
                    }
                }
            }
            return 0;

        }
        /// <summary>
        /// 获得问卷的绝对地址
        /// </summary>
        /// <param name="QuestionFileName">文件名</param>
        /// <returns>文件绝对地址</returns>
        private string GetQuestionPath(string QuestionFileName)
        {
            return Server.MapPath("~/xml/" + QuestionFileName);
        }
    }
}